표준SQL 테이블 조인

2019-06-19

표준 SQL 개요

ANSI/ISO SQL2의 경우 표준에 대한 명세가 부족하여, DBMS 벤더 별로 문법이나 용어의 차이가 너무 커져 상호 호환성 등의 문제가 발생했다. SQL에서 필요한 기능을 정리하고 호환 가능한 여러 기준을 제정한 것이 1999년에 정해진 ANSI/ISO SQL3이다.

표준 SQL 제정·개정

현재 사용되는 데이터베이스는 대부분 SQL-2003 표준을 기준으로 하고 있다.

1970년: 에드거 F. 커드 박사 관계형 DBMS(Relational DB) 논문 발표
1974년: IBM SQL 개발
1979년: Oracle 상용 DBMS 발표
1980년: Sybase SQL Server 발표 (이후 Sybase ASE로 개명)
1983년: IBM DB2 발표
1986년: ANSI/ISO SQL 표준 최초 제정 (SQL-86, SQL1)
1992년: ANSI/ISO SQL 표준 개정(SQL-92, SQL2)
1993년: MS SQL Server 발표 (Windows OS, Sybase Code 활용)
1999년: ANSI/ISO SQL 표준 개정(SQL-99, SQL3)
2003년: ANSI/ISO SQL 표준 개정(SQL-2003)
2008년: ANSI/ISO SQL 표준 개정(SQL-2008)

표준 SQL 기능

STANDARD JOIN 기능 추가
SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능
ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능
WINDOWN FUNCTION 같은 새로운 개념의 분석 기능

FROM절 JOIN 형태

관계형 DBMS 간 호환성 확보 및 JOIN 조건과 WHERE 절 검색 조건을 명확하게 표시하기 위하여, STANDARD JOIN 사용을 적극 권장한다. ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다.

INNER JOIN

INNER JOIN은 내부 JOIN이라고 하며, JOIN 조건에서 동일한 값이 있는 레코드만 반환한다. INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 뜻이므로 USING이나 ON 조건절을 필수적으로 사용해야 한다.

INNER JOIN은 JOIN에 사용된 같은 이름의 속성을 별개의 속성으로 표시한다.
USING 조건절 사용시 같은 이름을 가진 속성들 중에서 원하는 속성만 선택적으로 EQUI JOIN 할 수 있다.
ON조건절은 속성명이 다르더라도 JOIN 조건을 사용할 수 있다.
USING이나 ON조건절은 JOIN 서술부(USING, ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 가독성이 좋고 이해가 쉽다.

/* WHERE 절에 JOIN 조건 존재 */
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM   EMP, DEPT
WHERE  EMP.DEPTNO = DEPT.DEPTNO;

/* FROM 절 JOIN 조건 USING 절 */
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM   EMP INNER JOIN DEPT -- INNER 키워드는 생략 가능
USING  (DEPTNO)
WHERE  1=1;

/* FROM 절 JOIN 조건 ON 절 */
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM   EMP INNER JOIN DEPT -- INNER 키워드는 생략 가능
  ON   EMP.DEPTNO = DEPT.DEPTNO
WHERE  1=1;

/* 다중 테이블 JOIN */
SELECT E.EMPNO, D.DEPTNO, D.DNAME, F.DNAME AS DNAME1
FROM   EMP E JOIN DEPT D
  ON   (E.DEPTNO = D.DEPTNO)
       JOIN DEPT F
  ON   (E.DEPTNO = F.DEPTNO)
WHERE  1=1;

NATURAL JOIN

NATURAL JOIN은 INNER JOIN의 하위 개념으로 두 테이블 간 동일한 이름을 갖는 모든 속성들에 대해 EQUI JOIN을 수행한다. NATURAL JOIN이 명시되면 USING 및 ON 조건절, WHERE 절에서의 JOIN 조건을 정의할 수 없다.

NATURAL JOIN은 JOIN에 사용된 같은 이름의 속성을 하나로 처리한다.
별도 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 속성들이 다른 속성보다 먼저 출력된다.
SQL Server에서는 지원하지 않는 기능이다.

SELECT DEPTNO, EMPNO, ENAME, DNAME -- EMP.DEPTNO(X)
FROM   EMP NATURAL JOIN DEPT;

CROSS JOIN

두 개의 테이블에 CARTESIAN PRODUCT 또는 CROSS PRODUCT 연산을 수행한 것으로, 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합(M*N)을 말한다.

정상적인 경우라면 CROSS PRODUCT 연산이 필요한 경우가 많지 않지만, 튜닝이나 레포트 작성을 위해 고의로 사용하는 경우가 있을 수 있다.
데이터웨어하우스의 개별 DIMENSION을 FACT 속성과 JOIN하기 전에 모든 DIMENSION의 CROSS PRODUCT를 먼저 구할 때 유용하게 사용할 수 있다.

SELECT ENAME, DNAME
FROM   EMP CROSS JOIN DEPT
ORDER BY ENAME;

OUTER JOIN

INNER JOIN과 대비하여 OUTER JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 레코드가 필요할 때 사용할 수 있다.

Oracle의 경우 JOIN 속성 뒤에 (+)를 사용해도 OUTER JOIN이 가능하다.
(+) 표시는 FULL OUTER JOIN을 지원하지 않는다. 단, LEFT, RIGHT JOIN의 결과를 UNION 하면 FULL OUTER JOIN의 효과를 낼 수 있다.

/* LEFT OUTER JOIN */
SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_CNT, HOMETEAM_ID, TEAM_NAME
FROM   STADIUM LEFT OUTER JOIN TEAM -- OUTER 키워드는 생략 가능
  ON   STADIUM.HOMETEAM_ID = TEAM.TEAM_ID;

/* RIGHT OUTER JOIN  */
SELECT E.ENAME, D.DEPTNO, D.DNAME, D.LOC
FROM   EMP E RIGHT OUTER JOIN DEPT D -- OUTER 키워드는 생략 가능
  ON   E.DEPTNO = D.DEPTNO;

/* FULL OUTER JOIN = RIGHT OUTER JOIN UNION LEFT OUTER JOIN */
SELECT E.ENAME, D.DEPTNO, D.DNAME, D.LOC
FROM   EMP E FULL OUTER JOIN DEPT D -- OUTER 키워드는 생략 가능
  ON   E.DEPTNO = D.DEPTNO;

SELF JOIN

셀프 조인이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다.

동일 테이블 간 식별을 위해 반드시 테이블 별칭(Alias)을 사용해야 한다.

SELECT WORKER.EMPNO, WORKER.ENAME, MANAGER.ENAME
FROM   EMP WORKER, EMP MANAGER -- Alias 사용 필수
WHERE  WORKER.MGR = MANAGER.EMPNO;

집합 연산자(SET OPERATION)

집합 연산자를 사용하기 위해서는 다음의 제약 조건을 만족해야 한다.

SELECT 절의 속성 수가 동일해야 한다.
SELECT 절의 동일 위치에 존재하는 속성의 데이터 타입이 상호호환 가능해야 한다.

UNION

여러 SQL문 수행결과의 합집합이다. 중복된 레코드는 하나로 만들어 결과에 표시한다.

SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  TEAM_ID = 'TOT'
UNION
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  PLAYER_POSITION = 'FW';

UNION ALL

여러 SQL문 수행결과의 합집합이다. 중복된 레코드를 제외시키지 않고 그대로 결과에 표시한다.

개별 SQL문의 결과가 서로 중복되지 않는 경우 UNION과 결과가 동일하다.

SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  TEAM_ID = 'TOT'
UNION ALL
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  PLAYER_POSITION = 'FW';

INTERSECT

여러 SQL문 수행결과의 교집합이다. 중복된 레코드는 하나로 만들어 결과에 표시한다.

INTERSECT 연산자는 EXISTS 또는 IN 서브쿼리를 이용한 SQL문으로 변경 가능하다.

/* INTERSECT 키워드 사용 */
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  TEAM_ID = 'TOT'
INTERSECT
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  PLAYER_POSITION = 'FW';

/* EXISTS 사용 */
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  TEAM_ID = 'TOT'
  AND  EXISTS (SELECT 1 FROM PLAYER Y
               WHERE Y.PLAYER_ID = PLAYER_ID AND Y.POSITION = 'FW');

/* IN 서브쿼리 사용 */
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  TEAM_ID = 'TOT'
  AND  PLAYER_ID IN (SELECT Y.PLAYER_ID FROM PLAYER Y WHERE Y.POSITION = 'FW');

MINUS, EXCPET

앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 레코드는 하나로 만들어 결과에 표시한다.

Oracle MINUS 키워드 사용
SQL Server MINUS 대신 EXCPET 사용

/* MINUS 키워드 사용*/
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  TEAM_ID = 'TOT'
MINUS
SELECT TEAM_ID, PLAYER_NAME, PLAYER_POSITION, BACK_NO, HEIGHT
FROM   PLAYER
WHERE  PLAYER_POSITION = 'FW';

Blog Powered by Hexo

DatabaseSQL